﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class TreeViewDAL : ITreeViewDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public TreeViewDAL()
        {

        }

        public IList<TreeViewInfo> LoadDistrictTreeView(string parent_id, bool fastMode = false)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeViewInfo> infoList = new List<TreeViewInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@parent_id", parent_id);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT main.* FROM( "
                        + Environment.NewLine + " SELECT CAST('0' AS CHAR) AS ID "
                            + Environment.NewLine + ", CAST('ข้อมูลตำบล/อำเภอ/จังหวัด/ภาค' AS CHAR) AS NAME"
                            + Environment.NewLine + ", NULL AS PARENT_ID"
                            + Environment.NewLine + ", (select count(region_seq) from mas_regions"
                                + Environment.NewLine + " where record_status not like @DeleteRecord"
                             + Environment.NewLine + ") AS CHILDREN_COUNT"
                            + Environment.NewLine + ", '../Images/Buttons/xx.gif' AS IMAGE_URL"
                            + Environment.NewLine + ", NULL AS DESCRIPTION"

                        + Environment.NewLine + " UNION ALL "// ภาค
                        + Environment.NewLine + " SELECT CAST(CONCAT('0', '-', r.REGION_SEQ) AS CHAR) AS ID "
                            + Environment.NewLine + ", r.REGION_NAME AS NAME"
                            + Environment.NewLine + ", CAST('0' AS CHAR) AS PARENT_ID"
                            + Environment.NewLine + ", (select count(prov_seq) from mas_provinces"
                                + Environment.NewLine + " where record_status not like @DeleteRecord"
                                + Environment.NewLine + " and region_seq = r.region_seq"
                            + Environment.NewLine + ") AS CHILDREN_COUNT"
                            + Environment.NewLine + ", '../Images/Buttons/box_orange.gif' AS IMAGE_URL"
                            + Environment.NewLine + ", NULL AS DESCRIPTION"
                        + Environment.NewLine + " FROM MAS_REGIONS r"
                            + Environment.NewLine + " WHERE r.RECORD_STATUS not like @DeleteRecord"

                        + Environment.NewLine + " UNION ALL "// จังหวัด
                        + Environment.NewLine + " SELECT CAST(CONCAT(p.REGION_SEQ, '-', p.PROV_SEQ) AS CHAR) AS ID "
                            + Environment.NewLine + ", p.PROV_NAME AS NAME"
                            + Environment.NewLine + ", CAST(CONCAT('0', '-', p.REGION_SEQ) AS CHAR) AS PARENT_ID"
                            + Environment.NewLine + ", (select count(amp_seq) from mas_amphurs"
                                + Environment.NewLine + " where record_status not like @DeleteRecord"
                                + Environment.NewLine + " and prov_seq = p.prov_seq"
                            + Environment.NewLine + ") AS CHILDREN_COUNT"
                            + Environment.NewLine + ", '../Images/Buttons/box_green.gif' AS IMAGE_URL"
                            + Environment.NewLine + ", NULL AS DESCRIPTION"
                        + Environment.NewLine + " FROM MAS_PROVINCES p"
                            + Environment.NewLine + " WHERE p.RECORD_STATUS not like @DeleteRecord"

                        + Environment.NewLine + " UNION ALL "// อำเภอ
                        + Environment.NewLine + " SELECT CAST(CONCAT(a.PROV_SEQ, '-', a.AMP_SEQ) AS CHAR) AS ID"
                            + Environment.NewLine + ", a.AMP_NAME AS NAME"
                            + Environment.NewLine + ", CAST(CONCAT(p.REGION_SEQ, '-', a.PROV_SEQ) AS CHAR) AS PARENT_ID"
                            + Environment.NewLine + ", /* (select count(district_seq) from mas_districts"
                                + Environment.NewLine + " where record_status not like @DeleteRecord"
                                + Environment.NewLine + " and amp_seq = a.amp_seq "
                            + Environment.NewLine + ") */ 1 AS CHILDREN_COUNT"
                            + Environment.NewLine + ", '../Images/Buttons/box_blue.gif' AS IMAGE_URL"
                            + Environment.NewLine + ", NULL AS DESCRIPTION"
                        + Environment.NewLine + " FROM MAS_AMPHURS a"
                            + Environment.NewLine + " LEFT JOIN MAS_PROVINCES p ON a.PROV_SEQ = p.PROV_SEQ"
                            + Environment.NewLine + " WHERE a.RECORD_STATUS not like @DeleteRecord"
                                + Environment.NewLine + " AND p.RECORD_STATUS not like @DeleteRecord"
                        + Environment.NewLine + " UNION ALL "//ตำบล
                        + Environment.NewLine + " SELECT CAST(CONCAT(d.AMP_SEQ, '-', d.DISTRICT_SEQ) AS CHAR) AS ID"
                            + Environment.NewLine + ", d.DISTRICT_NAME AS NAME"
                            + Environment.NewLine + ", CAST(CONCAT(a.PROV_SEQ, '-', d.AMP_SEQ) AS CHAR) AS PARENT_ID"
                            + Environment.NewLine + ", 0 AS CHILDREN_COUNT"
                            + Environment.NewLine + ", '../Images/Buttons/stop_all.gif' AS IMAGE_URL"
                            + Environment.NewLine + ", NULL AS DESCRIPTION"
                        + Environment.NewLine + " FROM MAS_DISTRICTS d"
                            + Environment.NewLine + " LEFT JOIN MAS_AMPHURS a ON d.AMP_SEQ = a.AMP_SEQ"
                            + Environment.NewLine + " WHERE d.RECORD_STATUS not like @DeleteRecord"
                                + Environment.NewLine + " AND a.RECORD_STATUS not like @DeleteRecord"
                        + Environment.NewLine + ") main"
                        //+ Environment.NewLine + " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END"
                        + Environment.NewLine + (string.IsNullOrEmpty(parent_id) ? " where PARENT_ID IS NULL" : " WHERE PARENT_ID = @parent_id")
                        + Environment.NewLine + " ORDER BY NAME";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        var info = new TreeViewInfo
	                        {
		                        Id = Convert.IsDBNull(dr[i]["ID"]) ? "" : (dr[i]["ID"]).ToString(),
		                        Name = Convert.IsDBNull(dr[i]["NAME"]) ? "" : (dr[i]["NAME"]).ToString(),
		                        Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? "" : (dr[i]["PARENT_ID"]).ToString(),
		                        Descr = Convert.IsDBNull(dr[i]["DESCRIPTION"]) ? "" : (dr[i]["DESCRIPTION"]).ToString(),
		                        Children_count = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]),
		                        Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString()
	                        };

	                    infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();

            }




            return infoList;
        }
        public IList<TreeViewInfo> LoadJobSiteTreeView(Nullable<int> JobSiteSeq, Nullable<int> JobSiteTypeSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeViewInfo> infoList = new List<TreeViewInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@JobSiteSeq", JobSiteSeq);
                dbManager.AddParameters(1, "@JobSiteTypeSeq", JobSiteTypeSeq);
                dbManager.AddParameters(2, "@JobSiteUpper", "%/" + JobSiteSeq.ToString() + "/%");
                dbManager.AddParameters(3, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(4, "@CancelRecord", SystemInfo._CancelRecordStatus);

                strSQL = "SELECT j.JOB_SITE_SEQ, j.JOB_SITE_NAME"
                    + ", CASE WHEN j.JOB_SITE_SEQ = @JobSiteSeq"
                        + " THEN null"
                        + " ELSE j.upper_job_site_seq"
                        + " END AS upper_job_site_seq"
                    + ", CASE j.JOB_SITE_LEVEL_NO "
                        + " WHEN 1 THEN '../Images/Buttons/xx.gif'"
                        + " WHEN 2 THEN '../Images/Buttons/folder_plus_small.gif'"
                        + " WHEN 3 THEN '../Images/Buttons/folder_small.gif'"
                        + " WHEN 4 THEN '../Images/Buttons/stop_all.gif'"
                        + " ELSE '' END AS IMAGE_URL"
                    + " FROM HRM_MP_JOB_SITES j"
                    + " LEFT JOIN HRM_MP_JOB_SITE_LEVELD l ON j.JOB_SITE_LEVEL_SEQ = l.JOB_SITE_LEVEL_SEQ"
                    + " WHERE j.RECORD_STATUS NOT IN (@DeleteRecord, @CancelRecord)"
                        + " AND CASE WHEN @JobSiteSeq IS NULL THEN 0 =0 ELSE j.JOB_SITE_FULL_PATH LIKE @JobSiteUpper END"
                        + " AND j.JOB_SITE_TYPE_SEQ = @JobSiteTypeSeq"
                        + " ORDER BY j.JOB_SITE_REF";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    TreeViewInfo Info = new TreeViewInfo();

                    Info.Id = rdr.IsDBNull(0) ? "" : rdr.GetString(1);
                    Info.Name = rdr.IsDBNull(1) ? "" : rdr.GetString(1);
                    Info.Parent_id = rdr.IsDBNull(2) ? "" : rdr.GetString(1);

                    infoList.Add(Info);
                }

                //ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                //dr = ds.Tables[0].Rows;
                //if (dr.Count > 0)
                //{
                //    for (int i = 0; i < dr.Count; i++)
                //    {
                //        TreeViewInfo Info = new TreeViewInfo();

                //        Info.Id = Convert.IsDBNull(dr[i]["JOB_SITE_SEQ"]) ? "" : (dr[i]["JOB_SITE_SEQ"]).ToString();
                //        Info.Name = Convert.IsDBNull(dr[i]["JOB_SITE_NAME"]) ? "" : (dr[i]["JOB_SITE_NAME"]).ToString();
                //        Info.Parent_id = Convert.IsDBNull(dr[i]["upper_job_site_seq"]) ? "" : (dr[i]["upper_job_site_seq"]).ToString();
                //        //Info.JobSites.Sequence = Convert.IsDBNull(dr[i]["upper_job_site_seq"]) ? (int?)null : Int32.Parse(dr[i]["upper_job_site_seq"].ToString());

                //        infoList.Add(Info);
                //    }
                //}
                return infoList;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public IList<TreeViewInfo> LoadJobSiteTreeViewForNWD(string parent_id, Nullable<int> NwdJobSite)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeViewInfo> infoList = new List<TreeViewInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@parent_id", parent_id);
                dbManager.AddParameters(2, "@NwdJobSite", NwdJobSite);

                strSQL = "SELECT main.* FROM( "
                            + "SELECT CAST(s.SCOPE_SEQ AS CHAR) AS ID"
                                + ", NULL AS PARENT_ID"
                                + ", s.SCOPE_NAME AS NAME"
                                + ", (select count(job_site_seq)"
                                    + " from hrm_mp_job_sites j"
                                    + " left join hrm_mp_work_areas a on j.work_area_seq = a.work_area_seq"
                                    + " inner join hrm_mp_job_site_levels l on j.job_site_level_seq = l.job_site_level_seq"
                                    + " where j.record_status not like @DeleteRecord"
                                        + " and l.job_site_level_no = 3" // LEVEL ต่ำกว่ากรม 1 ระดับ
                                        + " and a.scope_seq = s.scope_seq"
                                        + " and j.JOB_SITE_SEQ NOT IN ("
                                                + " select job_site_seq "
                                                + " from hrm_ps_nwd_job_sites"
                                                + " where non_working_seq = @NwdJobSite"
                                                    + " and record_status not like @DeleteRecord)"
                                    + ") AS CHILDREN_COUNT"
                                + ", NULL AS DESCRIPTION"
                                + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                            + " FROM HRM_MP_SCOPES s"
                            + " WHERE s.RECORD_STATUS not like @DeleteRecord"

                            + " UNION ALL"

                            + " SELECT CAST(CONCAT(wa.SCOPE_SEQ, '-', js.JOB_SITE_SEQ) AS CHAR) AS ID"
                                + ", CAST(wa.SCOPE_SEQ AS CHAR) AS PARENT_ID"
                                + ", js.JOB_SITE_NAME AS NAME"
                                + ", 0 AS CHILDREN_COUNT"
                                + ", NULL AS DESCRIPTION"
                                + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                            + " FROM HRM_MP_JOB_SITES js"
                            + " INNER JOIN HRM_MP_WORK_AREAS wa ON js.WORK_AREA_SEQ = wa.WORK_AREA_SEQ"
                            + " INNER JOIN HRM_MP_JOB_SITE_LEVELS lv ON js.JOB_SITE_LEVEL_SEQ = lv.JOB_SITE_LEVEL_SEQ"
                            + " WHERE js.RECORD_STATUS not like @DeleteRecord"
                                + " AND lv.JOB_SITE_LEVEL_NO = 3" // LEVEL ต่ำกว่ากรม 1 ระดับ
                                + " AND js.JOB_SITE_SEQ NOT IN ("
                                    + " select job_site_seq "
                                    + " from hrm_ps_nwd_job_sites"
                                    + " where non_working_seq = @NwdJobSite"
                                        + " and record_status not like @DeleteRecord)"
                        + ") main"
                        //+ " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END";
                        + (string.IsNullOrEmpty(parent_id) ? " where PARENT_ID IS NULL" : " WHERE PARENT_ID = @parent_id");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeViewInfo info = new TreeViewInfo();
                        info.Id = Convert.IsDBNull(dr[i]["ID"]) ? "" : (dr[i]["ID"]).ToString();
                        info.Name = Convert.IsDBNull(dr[i]["NAME"]) ? "" : (dr[i]["NAME"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? "" : (dr[i]["PARENT_ID"]).ToString();
                        info.Children_count = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                        info.Descr = Convert.IsDBNull(dr[i]["DESCRIPTION"]) ? "" : (dr[i]["DESCRIPTION"]).ToString();
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }
        public IList<TreeViewInfo> LoadPositionTypeTreeView(string parent_id, string EmployeeTypes)
        {
            string strSQL;
            int emp_type = EmployeeTypes.Length > 0 ? Int32.Parse(EmployeeTypes) : 0;

            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeViewInfo> infoList = new List<TreeViewInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@parent_id", parent_id);
                dbManager.AddParameters(1, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(2, "@EmployeeTypes", emp_type);

                    strSQL = "SELECT main.* FROM( "
                            + Environment.NewLine + " " + " SELECT CAST('0' AS CHAR) AS ID "
                            + Environment.NewLine + " " + ", CAST('ข้อมูลประเภทตำแหน่ง' AS CHAR) AS NAME"
                            + Environment.NewLine + " " + ", NULL AS PARENT_ID"
                            + Environment.NewLine + " " + ", (select count(pos_type_seq) from hrm_mp_position_types"
                                + Environment.NewLine + "    " + " where record_status = @ActiveRecord"
                                + Environment.NewLine + "    " + (emp_type > 0 ? " and emp_type_seq = @EmployeeTypes" : "")
                             + Environment.NewLine + " " + ") AS CHILDREN_COUNT"
                            + Environment.NewLine + " " + ", '../Images/Buttons/xx.gif' AS IMAGE_URL"
                            + Environment.NewLine + " " + ", NULL AS DESCRIPTION"
                                + Environment.NewLine
                            + Environment.NewLine + " " + " UNION ALL "
                                + Environment.NewLine
                            + Environment.NewLine + " " + " SELECT CAST(CONCAT('0', '-', pt.POS_TYPE_SEQ) AS CHAR) AS ID "
                            + Environment.NewLine + " " + ", pt.POS_TYPE_NAME AS NAME"
                            + Environment.NewLine + " " + ", CAST('0' AS CHAR) AS PARENT_ID"
                            + Environment.NewLine + " " + ", (select count(class_seq) from hrm_mp_classes"
                                + Environment.NewLine + "    " + " where record_status = @ActiveRecord"
                                + Environment.NewLine + "    " + " and pos_type_seq = pt.pos_type_seq"
                            + Environment.NewLine + " " + ") AS CHILDREN_COUNT"
                            + Environment.NewLine + " " + ", '../Images/Buttons/box_orange.gif' AS IMAGE_URL"
                            + Environment.NewLine + " " + ", pt.REMARKS AS DESCRIPTION"
                            + Environment.NewLine + " " + " FROM HRM_MP_POSITION_TYPES pt"
                            + Environment.NewLine + " " + " WHERE pt.RECORD_STATUS = @ActiveRecord"
                            + Environment.NewLine + " " + (emp_type > 0 ? " AND pt.EMP_TYPE_SEQ = @EmployeeTypes" : "")
                                + Environment.NewLine
                            + Environment.NewLine + " " + " UNION ALL "
                                + Environment.NewLine
                            + Environment.NewLine + " " + " SELECT CAST(CONCAT(c.POS_TYPE_SEQ, '-', c.CLASS_SEQ) AS CHAR) AS ID"
                            + Environment.NewLine + " " + ", c.CLASS_NAME AS NAME"
                            + Environment.NewLine + " " + ", CAST(CONCAT('0', '-', c.POS_TYPE_SEQ) AS CHAR) AS PARENT_ID"
                            + Environment.NewLine + " " + ", 0 AS CHILDREN_COUNT"
                            + Environment.NewLine + " " + ", '../Images/Buttons/box_green.gif' AS IMAGE_URL"
                            + Environment.NewLine + " " + ", NULL AS DESCRIPTION"
                            + Environment.NewLine + " " + " FROM HRM_MP_CLASSES c"
                            + Environment.NewLine + " " + " WHERE c.RECORD_STATUS = @ActiveRecord"
                            + Environment.NewLine + " " + (emp_type > 0 ? " AND c.EMP_TYPE_SEQ = @EmployeeTypes" : "")
                            + Environment.NewLine + " " + ") main"
                        //+ Environment.NewLine + " " + " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END";
                        + Environment.NewLine + " " +(string.IsNullOrEmpty(parent_id) ? " where PARENT_ID IS NULL" : " WHERE PARENT_ID = " + "'"+ parent_id +"'" );
                
                
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeViewInfo info = new TreeViewInfo();
                        info.Id = Convert.IsDBNull(dr[i]["ID"]) ? "" : (dr[i]["ID"]).ToString();
                        info.Name = Convert.IsDBNull(dr[i]["NAME"]) ? "" : (dr[i]["NAME"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? "" : (dr[i]["PARENT_ID"]).ToString();
                        info.Descr = Convert.IsDBNull(dr[i]["DESCRIPTION"]) ? "" : (dr[i]["DESCRIPTION"]).ToString();
                        info.Children_count = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
        public IList<TreeViewInfo> LoadFieldTreeViewForDuty(Nullable<int> EmpTypeSeq, string parent_id)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeViewInfo> infoList = new List<TreeViewInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@EmpTypeSeq", EmpTypeSeq);
                dbManager.AddParameters(1, "@parent_id", parent_id);
                dbManager.AddParameters(2, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(3, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

                    strSQL = "SELECT main.* FROM " // Header
                            + "(SELECT CAST(pt.POS_TYPE_SEQ AS CHAR) AS ID"
                            + ", NULL AS PARENT_ID"
                            + ", CONCAT('ตำแหน่งประเภท', pt.POS_TYPE_NAME) AS NAME"
                            + ", NULL AS DESCRIPTION"
                            + ", (select count(field_pos_seq) from hrm_mp_field_positions "
                                + " where record_status not like @DeleteRecord"
                                + " and pos_type_seq = pt.pos_type_seq"
                            + ") AS CHILDREN_COUNT"
                            + ", '../Images/Buttons/xx.gif' AS IMAGE_URL"
                        + " FROM HRM_MP_POSITION_TYPES pt"
                        + " WHERE pt.RECORD_STATUS = @ActiveRecord"
                            + " AND pt.EMP_TYPE_SEQ = @EmpTypeSeq"
                        + " UNION ALL"
                        + " SELECT CAST(CONCAT(fp.POS_TYPE_SEQ, '-', fp.FIELD_POS_SEQ) AS CHAR) AS ID"
                            + ", CAST(fp.POS_TYPE_SEQ AS CHAR) AS PARENT_ID" // ตำแหน่งสายงาน
                            + ", fp.FIELD_POS_LINE AS NAME"
                            + ", REMARKS AS DESCRIPTION"
                            + ", (select count(class_seq) "
                                + " from hrm_mp_class_spec_dtls sd"
                                    + " inner join hrm_mp_class_specifications cs on sd.class_spec_seq = cs.class_spec_seq"
                                    + " inner join hrm_mp_field_positions f on cs.class_spec_seq= f.class_spec_seq"
                                + " where sd.record_status = @ActiveRecord"
                                    + " and f.field_pos_seq = fp.field_pos_seq "
                            + ") AS CHILDREN_COUNT"
                            + ", '../Images/Buttons/box_orange.gif' AS IMAGE_URL"
                        + " FROM HRM_MP_FIELD_POSITIONS fp"
                        + " WHERE fp.RECORD_STATUS = @ActiveRecord"
                            + " AND fp.EMP_TYPE_SEQ = @EmpTypeSeq"
                        + " UNION ALL"
                        + " SELECT CAST(CONCAT(f.FIELD_POS_SEQ, '-', c.CLASS_SEQ) AS CHAR) AS ID" // ระดับ
                            + ", CAST(CONCAT(f.POS_TYPE_SEQ, '-', f.FIELD_POS_SEQ) AS CHAR) AS PARENT_ID"
                            + ", c.CLASS_NAME AS NAME"
                            + ", sd.REMARKS AS DESCRIPTION"
                            + ", 0 AS CHILDREN_COUNT"
                            + ", '../Images/Buttons/box_green.gif' AS IMAGE_URL"
                        + " FROM HRM_MP_CLASSES c"
                            + " INNER JOIN HRM_MP_CLASS_SPEC_DTLS sd ON c.CLASS_SEQ = sd.CLASS_SEQ"
                            + " INNER JOIN HRM_MP_CLASS_SPECIFICATIONS cs ON sd.CLASS_SPEC_SEQ = cs.CLASS_SPEC_SEQ"
                            + " INNER JOIN HRM_MP_FIELD_POSITIONS f ON cs.CLASS_SPEC_SEQ= f.CLASS_SPEC_SEQ"
                        + " WHERE c.RECORD_STATUS = @ActiveRecord"
                            + " AND c.EMP_TYPE_SEQ = @EmpTypeSeq"
                        + ") main"
                        //+ " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END";
                        + (string.IsNullOrEmpty(parent_id) ? " where PARENT_ID IS NULL" : " WHERE PARENT_ID = @parent_id");
                


                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeViewInfo info = new TreeViewInfo();
                        info.Id = Convert.IsDBNull(dr[i]["ID"]) ? "" : (dr[i]["ID"]).ToString();
                        info.Name = Convert.IsDBNull(dr[i]["NAME"]) ? "" : (dr[i]["NAME"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? "" : (dr[i]["PARENT_ID"]).ToString();
                        info.Descr = Convert.IsDBNull(dr[i]["DESCRIPTION"]) ? "" : (dr[i]["DESCRIPTION"]).ToString();
                        info.Children_count = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }
        public IList<TreeViewInfo> LoadPositionTreeView(Nullable<int> EmpTypeSeq, string parent_id)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeViewInfo> infoList = new List<TreeViewInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@EmpTypeSeq", EmpTypeSeq);
                dbManager.AddParameters(1, "@parent_id", parent_id);
                dbManager.AddParameters(2, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(3, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

                    strSQL = "SELECT main.* FROM( "
                            + "SELECT CAST(pt.POS_TYPE_SEQ AS CHAR) AS POS_SEQ, NULL AS POS_REF"
                                + ", NULL AS POS_NAME, NULL AS JOB_SITE_SEQ"
                                + ", NULL AS PARENT_ID"
                                + ", CAST(CONCAT('ตำแหน่งประเภท', pt.POS_TYPE_NAME) AS CHAR) AS POS_TEXT"
                                + ", NULL AS JOB_DESCRIPTION"
                                + ", (select count(field_pos_seq) from hrm_mp_field_positions "
                                    + " where record_status = @ActiveRecord"
                                    + " and pos_type_seq = pt.pos_type_seq"
                                + ") AS CHILDREN_COUNT"
                                + ", '../Images/Buttons/xx.gif' AS IMAGE_URL"
                                + " FROM HRM_MP_POSITION_TYPES pt"
                                + " WHERE pt.RECORD_STATUS = @ActiveRecord"
                                    + " AND pt.EMP_TYPE_SEQ = @EmpTypeSeq"
                            + " UNION ALL"
                                + " SELECT CAST(CONCAT(fp.POS_TYPE_SEQ, '-', fp.FIELD_POS_SEQ) AS CHAR) AS POS_SEQ"
                                    + ", NULL AS POS_REF, NULL AS POS_NAME, NULL AS JOB_SITE_SEQ"
                                    + ", CAST(fp.POS_TYPE_SEQ AS CHAR) AS PARENT_ID"
                                    + ", fp.FIELD_POS_LINE AS POS_TEXT"
                                    + ", NULL AS JOB_DESCRIPTION"
                                    + ", (select count(pos_seq) from hrm_mp_positions "
                                        + " where record_status = @ActiveRecord"
                                        + " and field_pos_seq = fp.field_pos_seq"
                                    + ") AS CHILDREN_COUNT"
                                    + ", '../Images/Buttons/box_orange.gif' AS IMAGE_URL"
                                + " FROM HRM_MP_FIELD_POSITIONS fp"
                                + " WHERE fp.RECORD_STATUS = @ActiveRecord"
                                    + " AND fp.EMP_TYPE_SEQ = @EmpTypeSeq"
                            + " UNION ALL"
                            + " SELECT CAST(CONCAT(p.FIELD_POS_SEQ, '-', p.POS_SEQ) AS CHAR) AS POS_SEQ"
                                + ", p.POS_REF, p.POS_NAME, p.JOB_SITE_SEQ"
                                + ", CAST(CONCAT(f.POS_TYPE_SEQ, '-', p.FIELD_POS_SEQ) AS CHAR) AS PARENT_ID"
                                + ", CAST(CONCAT(p.POS_REF, ' - '"
                                    //+ ", CASE WHEN p.EXEC_POS_SEQ = 999 OR ISNULL(p.EXEC_POS_SEQ) THEN p.POS_NAME"
                                    + ", CASE WHEN p.EXEC_POS_SEQ = 999 OR p.EXEC_POS_SEQ = null THEN p.POS_NAME"
                                        + " ELSE CONCAT(ex.EXEC_POS_NAME"
                                            //+ ", CASE WHEN ISNULL(j.JOB_SITE_NAME) THEN '' ELSE j.JOB_SITE_NAME END)"
                                            + ", CASE WHEN j.JOB_SITE_NAME = null THEN '' ELSE j.JOB_SITE_NAME END)"
                                        + " END"
                        //+ ", CASE WHEN ISNULL(e.EMP_SEQ) THEN ''"
                        //    + " ELSE CONCAT('(', np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME , ')')"
                        //+ " END"
                                + ") AS CHAR) AS POS_TEXT"
                                + ", p.JOB_DESCRIPTION"
                                + ", 0 AS CHILDREN_COUNT"
                                + ", '../Images/Buttons/box_green.gif' AS IMAGE_URL"
                            + " FROM HRM_MP_POSITIONS p"
                            + " LEFT JOIN HRM_MP_FIELD_POSITIONS f ON p.FIELD_POS_SEQ = f.FIELD_POS_SEQ"
                            + " LEFT JOIN HRM_MP_EXEC_POSITIONS ex ON p.EXEC_POS_SEQ = ex.EXEC_POS_SEQ"
                            + " LEFT JOIN HRM_MP_JOB_SITES j ON p.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                            + " LEFT JOIN HRM_PS_EMPLOYEES e ON p.POS_SEQ = e.POS_SEQ AND e.EMP_STATUS_SEQ = 1"
                            + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                            + " WHERE p.RECORD_STATUS = @ActiveRecord"
                                + " AND p.EMP_TYPE_SEQ = @EmpTypeSeq"
                            + ") main"
                            //+ " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END";
                            + (string.IsNullOrEmpty(parent_id) ? " where PARENT_ID IS NULL" : " WHERE PARENT_ID = @parent_id");
                

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeViewInfo info = new TreeViewInfo();
                        info.Id = Convert.IsDBNull(dr[i]["POS_SEQ"]) ? "" : (dr[i]["POS_SEQ"]).ToString();
                        info.Name = Convert.IsDBNull(dr[i]["POS_TEXT"]) ? "" : (dr[i]["POS_TEXT"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? "" : (dr[i]["PARENT_ID"]).ToString();
                        info.Descr = Convert.IsDBNull(dr[i]["JOB_DESCRIPTION"]) ? "" : (dr[i]["JOB_DESCRIPTION"]).ToString();
                        info.Children_count = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }
        public IList<TreeViewInfo> GetPositionResultSearch(Nullable<int> EmpTypeSeq, string text_search)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeViewInfo> infoList = new List<TreeViewInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@EmpTypeSeq", EmpTypeSeq);
                dbManager.AddParameters(1, "@text_search", text_search);
                dbManager.AddParameters(2, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT main.* FROM( "
                        + "SELECT CAST(pt.POS_TYPE_SEQ AS CHAR) AS ID"
                            + ", NULL AS PARENT_ID"
                            + ", CAST(CONCAT('ตำแหน่งประเภท', pt.POS_TYPE_NAME) AS CHAR) AS NAME"
                            + ", NULL AS JOB_DESCRIPTION"
                            + ", (select count(field_pos_seq) from hrm_mp_field_positions "
                                + " where record_status not like @DeleteRecord"
                                + " and pos_type_seq = pt.pos_type_seq"
                            + ") AS CHILDREN_COUNT"
                            + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                            + " FROM HRM_MP_POSITION_TYPES pt"
                            + " WHERE pt.RECORD_STATUS not like @DeleteRecord"
                                + " AND pt.EMP_TYPE_SEQ = @EmpTypeSeq"
                        + " UNION ALL"
                            + " SELECT CAST(CONCAT(fp.POS_TYPE_SEQ, '-', fp.FIELD_POS_SEQ) AS CHAR) AS ID"
                                + ", CAST(fp.POS_TYPE_SEQ AS CHAR) AS PARENT_ID"
                                + ", CAST(CONCAT('ชื่อสายงาน', fp.FIELD_POS_NAME) AS CHAR) AS NAME"
                                + ", NULL AS JOB_DESCRIPTION"
                                + ", (select count(pos_seq) from hrm_mp_positions "
                                    + " where record_status not like @DeleteRecord"
                                    + " and field_pos_seq = fp.field_pos_seq"
                                + ") AS CHILDREN_COUNT"
                                + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                            + " FROM HRM_MP_FIELD_POSITIONS fp"
                            + " WHERE fp.RECORD_STATUS not like @DeleteRecord"
                                + " AND fp.EMP_TYPE_SEQ = @EmpTypeSeq"
                        + " UNION ALL"
                        + " SELECT CAST(CONCAT(p.FIELD_POS_SEQ, '-', p.POS_SEQ) AS CHAR) AS ID"
                            + ", CAST(CONCAT(f.POS_TYPE_SEQ, '-', p.FIELD_POS_SEQ) AS CHAR) AS PARENT_ID"
                            + ", CAST(CONCAT(p.POS_REF, ' - '"
                                + ", CASE WHEN p.EXEC_POS_SEQ = 999 OR ISNULL(p.EXEC_POS_SEQ) THEN p.POS_NAME"
                                    + " ELSE CONCAT(ex.EXEC_POS_NAME"
                                        + ", CASE WHEN ISNULL(j.JOB_SITE_NAME) THEN '' ELSE j.JOB_SITE_NAME END)"
                                    + " END"
                                + ", CASE WHEN ISNULL(e.EMP_SEQ) THEN ''"
                                    + " ELSE CONCAT('(', np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME , ')')"
                                + " END"
                            + ") AS CHAR) AS NAME"
                            + ", p.JOB_DESCRIPTION"
                            + ", 0 AS CHILDREN_COUNT"
                            + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                        + " FROM HRM_MP_POSITIONS p"
                        + " LEFT JOIN HRM_MP_FIELD_POSITIONS f ON p.FIELD_POS_SEQ = f.FIELD_POS_SEQ"
                        + " LEFT JOIN HRM_MP_EXEC_POSITIONS ex ON p.EXEC_POS_SEQ = ex.EXEC_POS_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES j ON p.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEES e ON p.POS_SEQ = e.POS_SEQ AND e.EMP_STATUS_SEQ = 1"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " WHERE p.RECORD_STATUS not like @DeleteRecord"
                            + " AND p.EMP_TYPE_SEQ = @EmpTypeSeq"
                        + ") main"
                        + " WHERE 0 = 0 " //CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END"
                            + " AND (main.ID LIKE CONCAT('%', @text_search , '%')"
                                    + " OR main.NAME LIKE CONCAT('%', @text_search , '%')"
                                    + " OR main.PARENT_ID LIKE CONCAT('%', @text_search , '%')"
                                    + ")";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeViewInfo info = new TreeViewInfo();
                        info.Id = Convert.IsDBNull(dr[i]["ID"]) ? "" : (dr[i]["ID"]).ToString();
                        info.Name = Convert.IsDBNull(dr[i]["NAME"]) ? "" : (dr[i]["NAME"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? "" : (dr[i]["PARENT_ID"]).ToString();
                        info.Descr = Convert.IsDBNull(dr[i]["JOB_DESCRIPTION"]) ? "" : (dr[i]["JOB_DESCRIPTION"]).ToString();
                        info.Children_count = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }
        public IList<TreeViewInfo> LoadCountryZoneTreeView(string parent_id)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeViewInfo> infoList = new List<TreeViewInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@parent_id", parent_id);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT main.* FROM( "
                        + " SELECT CAST('0' AS CHAR) AS ID "
                            + ", CAST('ข้อมูลภูมิภาค' AS CHAR) AS NAME"
                            + ", NULL AS PARENT_ID"
                            + ", (select count(zone_seq) from mas_country_zones"
                                + " where record_status not like @DeleteRecord"
                             + ") AS CHILDREN_COUNT"
                            + ", '../Images/Buttons/xx.gif' AS IMAGE_URL"
                            + ", NULL AS DESCRIPTION"

                        + " UNION ALL "

                        + " SELECT CAST(CONCAT('0', '-', z.ZONE_SEQ) AS CHAR) AS ID "
                            + ", z.ZONE_NAME AS NAME"
                            + ", CAST('0' AS CHAR) AS PARENT_ID"
                            + ", (select count(country_seq) from mas_countries"
                                + " where record_status not like @DeleteRecord"
                                + " and zone_seq = z.zone_seq"
                            + ") AS CHILDREN_COUNT"
                            + ", '../Images/Buttons/box_orange.gif' AS IMAGE_URL"
                            + ", z.REMARKS AS DESCRIPTION"
                        + " FROM MAS_COUNTRY_ZONES z"
                            + " WHERE z.RECORD_STATUS not like @DeleteRecord"
                        + " UNION ALL "
                        + " SELECT CAST(CONCAT(c.ZONE_SEQ, '-', c.COUNTRY_SEQ) AS CHAR) AS ID"
                            + ", c.COUNTRY_NAME AS NAME"
                            + ", CAST(CONCAT('0', '-', c.ZONE_SEQ) AS CHAR) AS PARENT_ID"
                            + ", (select count(city_seq) from mas_cities"
                                + " where record_status not like @DeleteRecord"
                                + " and country_seq = c.country_seq"
                            + ") AS CHILDREN_COUNT"
                            + ", '../Images/Buttons/box_green.gif' AS IMAGE_URL"
                            + ", NULL AS DESCRIPTION"
                        + " FROM MAS_COUNTRIES c"
                            + " WHERE c.RECORD_STATUS not like @DeleteRecord"

                        + " UNION ALL "
                        + " SELECT CAST(CONCAT(c.COUNTRY_SEQ, '-', c.CITY_SEQ) AS CHAR) AS ID"
                            + ", c.CITY_NAME AS NAME"
                            + ", CAST(CONCAT(cn.ZONE_SEQ, '-', c.COUNTRY_SEQ) AS CHAR) AS PARENT_ID"
                            + ", 0 AS CHILDREN_COUNT"
                            + ", '../Images/Buttons/box_blue.gif' AS IMAGE_URL"
                            + ", NULL AS DESCRIPTION"
                        + " FROM MAS_CITIES c"
                            + " LEFT JOIN MAS_COUNTRIES cn ON c.COUNTRY_SEQ = cn.COUNTRY_SEQ"
                            + " WHERE c.RECORD_STATUS not like @DeleteRecord"
                            + " AND cn.RECORD_STATUS not like @DeleteRecord"
                        + ") main"
                        //+ " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END";
                        + (string.IsNullOrEmpty(parent_id) ? " where PARENT_ID IS NULL" : " WHERE PARENT_ID = @parent_id");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeViewInfo info = new TreeViewInfo();
                        info.Id = Convert.IsDBNull(dr[i]["ID"]) ? "" : (dr[i]["ID"]).ToString();
                        info.Name = Convert.IsDBNull(dr[i]["NAME"]) ? "" : (dr[i]["NAME"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? "" : (dr[i]["PARENT_ID"]).ToString();
                        info.Descr = Convert.IsDBNull(dr[i]["DESCRIPTION"]) ? "" : (dr[i]["DESCRIPTION"]).ToString();
                        info.Children_count = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }
        public IList<TreeViewInfo> LoadJobSiteAreaTreeView(string parent_id)
        {
            string strSQL;
            IList<TreeViewInfo> infoList = new List<TreeViewInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@parent_id", parent_id);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(3, "@JobSiteType", SystemInfo._JobsiteExternal);
                dbManager.AddParameters(4, "@JobSiteLevel", 3);

                strSQL = "SELECT main.* FROM( "
                        + " SELECT CAST('0' AS CHAR) AS ID "
                            + ", CAST('การแบ่งเขตอาณาสำนักงานในต่างประเทศ' AS CHAR) AS NAME"
                            + ", NULL AS PARENT_ID"
                            + ", (select count(zone_seq) from mas_country_zones"
                                + " where record_status = @ActiveRecord"
                             + ") AS CHILDREN_COUNT"
                            + ", '../Images/Buttons/xx.gif' AS IMAGE_URL"
                            + ", NULL AS DESCRIPTION"

                        + " UNION ALL "

                        + " SELECT CAST('1' AS CHAR) AS ID "
                            + ", CAST('การแบ่งเขตอาณาต่างจังหวัด' AS CHAR) AS NAME"
                            + ", NULL AS PARENT_ID"
                            + ", (select count(j.job_site_seq) "
                                + " from hrm_mp_job_sites j"
                                + " where j.record_status = @ActiveRecord"
                                + " and j.work_area_seq = 2" // ส่วนกลางในภูมิภาค
                                + " and j.job_site_type_seq = @JobSiteType"
                             + ") AS CHILDREN_COUNT"
                            + ", '../Images/Buttons/xx.gif' AS IMAGE_URL"
                            + ", NULL AS DESCRIPTION"

                        + " UNION ALL "

                        + " SELECT CAST(CONCAT('0', '-', z.ZONE_SEQ) AS CHAR) AS ID "
                            + ", CAST(CONCAT('ภูมิภาค', z.ZONE_NAME) AS CHAR) AS NAME"
                            + ", CAST('0' AS CHAR) AS PARENT_ID"
                            + ", (select count(country_seq) from mas_countries"
                                + " where record_status = @ActiveRecord"
                                + " and zone_seq = z.zone_seq"
                                + " and exists (select country_seq "
                                        + " from hrm_mp_job_sites"
                                        + " where record_status = @ActiveRecord"
                                        + " and work_area_seq = 4" // เอาเฉพาะต่างประเทศ
                                        + " and job_site_type_seq = @JobSiteType"
                                        + ")"
                            + ") AS CHILDREN_COUNT"
                            + ", '../Images/Buttons/box_orange.gif' AS IMAGE_URL"
                            + ", z.REMARKS AS DESCRIPTION"
                        + " FROM MAS_COUNTRY_ZONES z"
                            + " WHERE z.RECORD_STATUS = @ActiveRecord"

                        + " UNION ALL "

                        + " SELECT CAST(CONCAT(c.ZONE_SEQ, '-', c.COUNTRY_SEQ) AS CHAR) AS ID"
                            + ", c.COUNTRY_NAME AS NAME"
                            + ", CAST(CONCAT('0', '-', c.ZONE_SEQ) AS CHAR) AS PARENT_ID"
                            + ", (select count(job_site_seq) from hrm_mp_job_sites"
                                + " where record_status = @ActiveRecord"
                                + " and country_seq = c.country_seq"
                                + " and job_site_type_seq = @JobSiteType"
                                + " and job_site_level_seq = @JobSiteLevel"
                            + ") AS CHILDREN_COUNT"
                            + ", '../Images/Buttons/box_green.gif' AS IMAGE_URL"
                            + ", NULL AS DESCRIPTION"
                        + " FROM MAS_COUNTRIES c"
                            + " WHERE c.RECORD_STATUS = @ActiveRecord"
                            //+ " AND c.COUNTRY_SEQ IN(select country_seq from hrm_mp_job_sites "
                            //    + " where record_status = @ActiveRecord"
                            //    + " and work_area_seq = 4"
                            //    + ")"
                        + " UNION ALL "

                        + " SELECT CAST(CONCAT(j.COUNTRY_SEQ, '-', j.JOB_SITE_SEQ) AS CHAR) AS ID"
                            + ", j.JOB_SITE_NAME AS NAME"
                            + ", CAST(CONCAT(c.ZONE_SEQ, '-', j.COUNTRY_SEQ) AS CHAR) AS PARENT_ID"
                            + ", (select count(job_site_area_seq) from hrm_mp_job_site_areas"
                                + " where record_status = @ActiveRecord"
                                + " and job_site_seq = j.job_site_seq"
                            + ") AS CHILDREN_COUNT"
                            + ", '../Images/Buttons/box_blue.gif' AS IMAGE_URL"
                            + ", NULL AS DESCRIPTION"
                        + " FROM HRM_MP_JOB_SITES j"
                            + " LEFT JOIN MAS_COUNTRIES c ON j.COUNTRY_SEQ = c.COUNTRY_SEQ"
                            + " WHERE j.RECORD_STATUS = @ActiveRecord"
                                + " AND j.JOB_SITE_FULL_PATH LIKE "
                                    + "(select concat('%/', curr_job_site_seq, '/%') from hrm_mp_job_site_types "
                                        + " where job_site_type_seq = @JobSiteType)"
                                + " AND j.WORK_AREA_SEQ = 4"// เอาเฉพาะต่างประเทศ
                                + " AND j.JOB_SITE_LEVEL_SEQ = @JobSiteLevel"

                        + " UNION ALL "
                        // ต่างจังหวัด
                        + " SELECT CAST(CONCAT('1', '-', j.JOB_SITE_SEQ) AS CHAR) AS ID"
                            + ", j.JOB_SITE_NAME AS NAME"
                            + ", CAST('1' AS CHAR) AS PARENT_ID"
                            + ", (select count(job_site_area_seq) from hrm_mp_job_site_areas"
                                + " where record_status = @ActiveRecord"
                                + " and job_site_seq = j.job_site_seq"
                            + ") AS CHILDREN_COUNT"
                            + ", '../Images/Buttons/box_blue.gif' AS IMAGE_URL"
                            + ", NULL AS DESCRIPTION"
                        + " FROM HRM_MP_JOB_SITES j"
                            + " LEFT JOIN MAS_COUNTRIES c ON j.COUNTRY_SEQ = c.COUNTRY_SEQ"
                            + " WHERE j.RECORD_STATUS = @ActiveRecord"
                                + " AND j.JOB_SITE_FULL_PATH LIKE "
                                    + "(select concat('%/', curr_job_site_seq, '/%') from hrm_mp_job_site_types "
                                        + " where job_site_type_seq = @JobSiteType)"
                                + " AND j.WORK_AREA_SEQ = 2"// เอาเฉพาะต่างจังหวัด

                        + " UNION ALL "

                        + " SELECT CAST(CONCAT(ja.JOB_SITE_SEQ, '-', ja.JOB_SITE_AREA_SEQ) AS CHAR) AS ID"
                            + ", ja.AREA_NAME AS NAME"
                            + ", CAST(CONCAT(j.COUNTRY_SEQ, '-', j.JOB_SITE_SEQ) AS CHAR) AS PARENT_ID"
                            + ", 0 AS CHILDREN_COUNT"
                            + ", '../Images/Buttons/stop_all.gif' AS IMAGE_URL"
                            + ", ja.REMARKS AS DESCRIPTION"
                        + " FROM HRM_MP_JOB_SITE_AREAS ja"
                            + " LEFT JOIN HRM_MP_JOB_SITES j ON ja.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                            + " WHERE ja.RECORD_STATUS = @ActiveRecord"

                        + " UNION ALL "
                        // กรณีต่างจังหวัด
                        + " SELECT CAST(CONCAT(ja.JOB_SITE_SEQ, '-', ja.JOB_SITE_AREA_SEQ) AS CHAR) AS ID"
                            + ", ja.AREA_NAME AS NAME"
                            + ", CAST(CONCAT('1', '-', j.JOB_SITE_SEQ) AS CHAR) AS PARENT_ID"
                            + ", 0 AS CHILDREN_COUNT"
                            + ", '../Images/Buttons/stop_all.gif' AS IMAGE_URL"
                            + ", ja.REMARKS AS DESCRIPTION"
                        + " FROM HRM_MP_JOB_SITE_AREAS ja"
                            + " LEFT JOIN HRM_MP_JOB_SITES j ON ja.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                            + " WHERE ja.RECORD_STATUS = @ActiveRecord"
                        + ") main"
                        //+ " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END";
                        + (string.IsNullOrEmpty(parent_id) ? " where PARENT_ID IS NULL" : " WHERE PARENT_ID = @parent_id");

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);
                while (rdr.Read())
                {
                    TreeViewInfo info = new TreeViewInfo();
                    info.Id = rdr.IsDBNull(0) ? "" : rdr.GetString(0);// Convert.IsDBNull(dr[i]["ID"]) ? "" : (dr[i]["ID"]).ToString();
                    info.Name = rdr.IsDBNull(1) ? "" : rdr.GetString(1);// Convert.IsDBNull(dr[i]["NAME"]) ? "" : (dr[i]["NAME"]).ToString();
                    info.Parent_id = rdr.IsDBNull(2) ? "" : rdr.GetString(2);// Convert.IsDBNull(dr[i]["PARENT_ID"]) ? "" : (dr[i]["PARENT_ID"]).ToString();
                    info.Descr = rdr.IsDBNull(5) ? "" : rdr.GetString(5);// Convert.IsDBNull(dr[i]["DESCRIPTION"]) ? "" : (dr[i]["DESCRIPTION"]).ToString();
                    info.Children_count = rdr.IsDBNull(3) ? 0 : rdr.GetInt32(3);// Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                    info.Image_url = rdr.IsDBNull(4) ? "" : rdr.GetString(4);// Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();

                    infoList.Add(info);
                }

                //ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                //dr = ds.Tables[0].Rows;
                //if (dr.Count > 0)
                //{
                //    for (int i = 0; i < dr.Count; i++)
                //    {
                //        TreeViewInfo info = new TreeViewInfo();
                //        info.Id = Convert.IsDBNull(dr[i]["ID"]) ? "" : (dr[i]["ID"]).ToString();
                //        info.Name = Convert.IsDBNull(dr[i]["NAME"]) ? "" : (dr[i]["NAME"]).ToString();
                //        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? "" : (dr[i]["PARENT_ID"]).ToString();
                //        info.Descr = Convert.IsDBNull(dr[i]["DESCRIPTION"]) ? "" : (dr[i]["DESCRIPTION"]).ToString();
                //        info.Children_count = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                //        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();

                //        infoList.Add(info);
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }
        public IList<TreeViewInfo> LoadNonWorkingDayTreeView(string Year, string parent_id)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeViewInfo> infoList = new List<TreeViewInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Year", Year);
                dbManager.AddParameters(1, "@parent_id", parent_id);
                dbManager.AddParameters(2, "@ActiveRecord", SystemInfo._ActiveRecordStatus);


                strSQL = "SELECT main.* FROM( "
                        + " SELECT CAST(@Year AS CHAR) AS ID, CAST(CONCAT('ปี งบประมาณ', @Year) AS CHAR) AS NAME"
                            + ", NULL AS PARENT_ID"
                            + ", NULL AS DESCRIPTION"
                            + ", 12 AS CHILDREN_COUNT"
                            + ", '../Images/Buttons/listItemArrow.gif' AS IMAGE_URL"
                            + ", NULL AS NWD1 "
                        + " UNION ALL"
                            + " SELECT CAST(CONCAT(@Year, '-', '10') AS CHAR) AS ID"
                                + ", CONCAT(CAST('ตุลาคม' AS CHAR), ' ', @Year) AS NAME"
                                + ", CAST(@Year AS CHAR) AS PARENT_ID"
                                + ", NULL AS DESCRIPTION"
                                + ", (select count(non_working_seq)"
                                    + " from hrm_ps_non_working_days"
                                    + " where bg_year = @Year"
                                        + " and month(non_working_date) = 10"
                                        + " and record_status = @ActiveRecord"
                                    + ") AS CHILDREN_COUNT"
                                + ", '../Images/Buttons/box_blue.gif' AS IMAGE_URL"
                            + ", NULL AS NWD1 "
                        + " UNION ALL"
                            + " SELECT CAST(CONCAT(@Year, '-', '11') AS CHAR) AS ID, CONCAT(CAST('พฤศจิกายน' AS CHAR), ' ', @Year) AS NAME"
                                + ", CAST(@Year AS CHAR) AS PARENT_ID"
                                + ", NULL AS DESCRIPTION"
                                + ", (select count(non_working_seq)"
                                    + " from hrm_ps_non_working_days"
                                    + " where bg_year = @Year"
                                        + " and month(non_working_date) = 11"
                                        + " and record_status = @ActiveRecord"
                                    + ") AS CHILDREN_COUNT"
                                + ", '../Images/Buttons/box_blue.gif' AS IMAGE_URL"
                            + ", NULL AS NWD1 "
                        + " UNION ALL"
                            + " SELECT CAST(CONCAT(@Year, '-', '12') AS CHAR) AS ID, CONCAT(CAST('ธันวาคม' AS CHAR), ' ', @Year) AS NAME"
                                + ", CAST(@Year AS CHAR) AS PARENT_ID"
                                + ", NULL AS DESCRIPTION"
                                + ", (select count(non_working_seq)"
                                    + " from hrm_ps_non_working_days"
                                    + " where bg_year = @Year"
                                        + " and month(non_working_date) = 12"
                                        + " and record_status = @ActiveRecord"
                                    + ") AS CHILDREN_COUNT"
                                + ", '../Images/Buttons/box_blue.gif' AS IMAGE_URL"
                            + ", NULL AS NWD1 "
                        + " UNION ALL"
                            + " SELECT CAST(CONCAT(@Year, '-', '1') AS CHAR) AS ID, CONCAT(CAST('มกราคม' AS CHAR), ' ', (@Year + 1)) AS NAME"
                                + ", CAST(@Year AS CHAR) AS PARENT_ID"
                                + ", NULL AS DESCRIPTION"
                                + ", (select count(non_working_seq)"
                                    + " from hrm_ps_non_working_days"
                                    + " where bg_year = @Year"
                                        + " and month(non_working_date) = 1"
                                        + " and record_status = @ActiveRecord"
                                    + ") AS CHILDREN_COUNT"
                                + ", '../Images/Buttons/box_blue.gif' AS IMAGE_URL"
                            + ", NULL AS NWD1 "
                        + " UNION ALL"
                            + " SELECT CAST(CONCAT(@Year, '-', '2') AS CHAR) AS ID, CONCAT(CAST('กุมภาพันธ์' AS CHAR), ' ', (@Year + 1)) AS NAME"
                                + ", CAST(@Year AS CHAR) AS PARENT_ID"
                                + ", NULL AS DESCRIPTION"
                                + ", (select count(non_working_seq)"
                                    + " from hrm_ps_non_working_days"
                                    + " where bg_year = @Year"
                                        + " and month(non_working_date) = 2"
                                        + " and record_status = @ActiveRecord"
                                    + ") AS CHILDREN_COUNT"
                                + ", '../Images/Buttons/box_blue.gif' AS IMAGE_URL"
                            + ", NULL AS NWD1 "
                        + " UNION ALL"
                            + " SELECT CAST(CONCAT(@Year, '-', '3') AS CHAR) AS ID, CONCAT(CAST('มีนาคม' AS CHAR), ' ', (@Year + 1)) AS NAME"
                                + ", CAST(@Year AS CHAR) AS PARENT_ID"
                                + ", NULL AS DESCRIPTION"
                                + ", (select count(non_working_seq)"
                                    + " from hrm_ps_non_working_days"
                                    + " where bg_year = @Year"
                                        + " and month(non_working_date) = 3"
                                        + " and record_status = @ActiveRecord"
                                    + ") AS CHILDREN_COUNT"
                                + ", '../Images/Buttons/box_blue.gif' AS IMAGE_URL"
                            + ", NULL AS NWD1 "
                        + " UNION ALL"
                            + " SELECT CAST(CONCAT(@Year, '-', '4') AS CHAR) AS ID, CONCAT(CAST('เมษายน' AS CHAR), ' ', (@Year + 1)) AS NAME"
                                + ", CAST(@Year AS CHAR) AS PARENT_ID"
                                + ", NULL AS DESCRIPTION"
                                + ", (select count(non_working_seq)"
                                    + " from hrm_ps_non_working_days"
                                    + " where bg_year = @Year"
                                        + " and month(non_working_date) = 4"
                                        + " and record_status = @ActiveRecord"
                                    + ") AS CHILDREN_COUNT"
                                + ", '../Images/Buttons/box_blue.gif' AS IMAGE_URL"
                            + ", NULL AS NWD1 "
                        + " UNION ALL"
                            + " SELECT CAST(CONCAT(@Year, '-', '5') AS CHAR) AS ID, CONCAT(CAST('พฤษภาคม' AS CHAR), ' ', (@Year + 1)) AS NAME"
                                + ", CAST(@Year AS CHAR) AS PARENT_ID"
                                + ", NULL AS DESCRIPTION"
                                + ", (select count(non_working_seq)"
                                    + " from hrm_ps_non_working_days"
                                    + " where bg_year = @Year"
                                        + " and month(non_working_date) = 5"
                                        + " and record_status = @ActiveRecord"
                                    + ") AS CHILDREN_COUNT"
                                + ", '../Images/Buttons/box_blue.gif' AS IMAGE_URL"
                            + ", NULL AS NWD1 "
                        + " UNION ALL"
                            + " SELECT CAST(CONCAT(@Year, '-', '6') AS CHAR) AS ID, CONCAT(CAST('มิถุนายน' AS CHAR), ' ', (@Year + 1)) AS NAME"
                                + ", CAST(@Year AS CHAR) AS PARENT_ID"
                                + ", NULL AS DESCRIPTION"
                                + ", (select count(non_working_seq)"
                                    + " from hrm_ps_non_working_days"
                                    + " where bg_year = @Year"
                                        + " and month(non_working_date) = 6"
                                        + " and record_status = @ActiveRecord"
                                    + ") AS CHILDREN_COUNT"
                                + ", '../Images/Buttons/box_blue.gif' AS IMAGE_URL"
                            + ", NULL AS NWD1 "
                        + " UNION ALL"
                            + " SELECT CAST(CONCAT(@Year, '-', '7') AS CHAR) AS ID, CONCAT(CAST('กรกฎาคม' AS CHAR), ' ', (@Year + 1)) AS NAME"
                                + ", CAST(@Year AS CHAR) AS PARENT_ID"
                                + ", NULL AS DESCRIPTION"
                                + ", (select count(non_working_seq)"
                                    + " from hrm_ps_non_working_days"
                                    + " where bg_year = @Year"
                                        + " and month(non_working_date) = 7"
                                        + " and record_status = @ActiveRecord"
                                    + ") AS CHILDREN_COUNT"
                                + ", '../Images/Buttons/box_blue.gif' AS IMAGE_URL"
                            + ", NULL AS NWD1 "
                        + " UNION ALL"
                            + " SELECT CAST(CONCAT(@Year, '-', '8') AS CHAR) AS ID, CONCAT(CAST('สิงหาคม' AS CHAR), ' ', (@Year + 1)) AS NAME"
                                + ", CAST(@Year AS CHAR) AS PARENT_ID"
                                + ", NULL AS DESCRIPTION"
                                + ", (select count(non_working_seq)"
                                    + " from hrm_ps_non_working_days"
                                    + " where bg_year = @Year"
                                        + " and month(non_working_date) = 8"
                                        + " and record_status = @ActiveRecord"
                                    + ") AS CHILDREN_COUNT"
                                + ", '../Images/Buttons/box_blue.gif' AS IMAGE_URL"
                            + ", NULL AS NWD1 "
                        + " UNION ALL"
                            + " SELECT CAST(CONCAT(@Year, '-', '9') AS CHAR) AS ID, CONCAT(CAST('กันยายน' AS CHAR), ' ', (@Year + 1)) AS NAME"
                                + ", CAST(@Year AS CHAR) AS PARENT_ID"
                                + ", NULL AS DESCRIPTION"
                                + ", (select count(non_working_seq)"
                                    + " from hrm_ps_non_working_days"
                                    + " where bg_year = @Year"
                                        + " and month(non_working_date) = 9"
                                        + " and record_status = @ActiveRecord"
                                    + ") AS CHILDREN_COUNT"
                                + ", '../Images/Buttons/box_blue.gif' AS IMAGE_URL"
                            + ", NULL AS NWD1 "
                        + " UNION ALL"
                            + " SELECT CAST(d.NON_WORKING_SEQ AS CHAR) AS ID "
                                + ", CONCAT(DAY(d.NON_WORKING_DATE), ' - ', d.NON_WORKING_NAME) AS NAME"
                                + ", CAST(CONCAT(@Year, '-', MONTH(d.NON_WORKING_DATE)) AS CHAR) AS PARENT_ID"
                                + ", NULL AS DESCRIPTION"
                                + ", 0 AS CHILDREN_COUNT"
                                + ", '../Images/Buttons/box_green.gif' AS IMAGE_URL"
                                + ", d.NON_WORKING_DATE AS NWD1 "
                                + " FROM HRM_PS_NON_WORKING_DAYS d"
                                    + " WHERE d.RECORD_STATUS = @ActiveRecord"
                                    + " and d.bg_year = @Year " 
                        + ") main"
                    // + " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END";
                    + (parent_id == "" ? " where PARENT_ID IS NULL" : 
                        parent_id == null ? "":" WHERE PARENT_ID = @parent_id")+ 
                    " Order by NWD1 ";


                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeViewInfo info = new TreeViewInfo();
                        info.Id = Convert.IsDBNull(dr[i]["ID"]) ? "" : (dr[i]["ID"]).ToString().Trim();
                        info.Name = Convert.IsDBNull(dr[i]["NAME"]) ? "" : (dr[i]["NAME"]).ToString().Trim();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? "" : (dr[i]["PARENT_ID"]).ToString().Trim();
                        info.Descr = Convert.IsDBNull(dr[i]["DESCRIPTION"]) ? "" : (dr[i]["DESCRIPTION"]).ToString().Trim();
                        info.Children_count = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString().Trim();

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }
        public IList<TreeViewInfo> LoadAbsenceTreeView(string JobSite, string Employee, string parent_id)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeViewInfo> infoList = new List<TreeViewInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@JobSite", "%/" + JobSite.ToString() + "/%");
                dbManager.AddParameters(1, "@Employee", Employee);
                dbManager.AddParameters(2, "@parent_id", parent_id);
                dbManager.AddParameters(3, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT main.* FROM( "
                         + " SELECT CAST('0' AS CHAR) AS ID"
                             + ", NULL AS PARENT_ID"
                             + ", CAST('ข้อมูลรายการใบลา' AS CHAR) AS NAME"                             
                             + ", NULL AS DESCRIPTION"
                             + ",(select count( date(created_when) ) "
                                + " from hrm_ps_absences"
                                + " where record_status not like @DeleteRecord "
                                //+ " group by date(created_when)"
                             + " ) AS CHILDREN_COUNT"
                             + ", '../Images/Buttons/listItemArrow.gif' AS IMAGE_URL"
                    + " UNION ALL"
                    + " SELECT CAST(CONCAT('0', '-', DATE(a.CREATED_WHEN)) AS CHAR) AS ID"
                        + ", CAST('0' AS CHAR) AS PARENT_ID"
                        + ", CAST(DATE(a.CREATED_WHEN) AS CHAR) AS NAME"
                        + ", '' AS DESCRIPTION"
                        + ", (select count(absence_seq) "
                            + " from hrm_ps_absences"
                            + " where record_status not like @DeleteRecord"
                                + " and date(created_when) = date(a.created_when) "
                        + ") AS CHILDREN_COUNT"
                        + ", '../Images/Buttons/bgreen.gif' AS IMAGE_URL"
                    + " FROM HRM_PS_ABSENCES a"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e ON a.EMP_SEQ = e.EMP_SEQ"
                    + " WHERE a.RECORD_STATUS not like @DeleteRecord"
                    + " GROUP BY DATE(a.CREATED_WHEN)"
                    + " UNION ALL"
                    + " SELECT CAST(a.ABSENCE_SEQ AS CHAR) AS ID"
                        + ", CAST(CONCAT('0', '-', DATE(a.CREATED_WHEN)) AS CHAR) AS PARENT_ID"
                        + ", CAST(CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS CHAR) AS NAME"
                        + ", a.REMARKS AS DESCRIPTION"
                        + ", 0 AS CHILDREN_COUNT"
                        + ", '../Images/Buttons/forums.gif' AS IMAGE_URL"
                    + " FROM HRM_PS_ABSENCES a"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e ON a.EMP_SEQ = e.EMP_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    //+ " LEFT JOIN HRM_MP_JOB_SITES j ON e.WORKING_JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                    + " WHERE a.RECORD_STATUS not like @DeleteRecord"
                    //+ (JobSite > 0 ? " AND j.FULL_PATH LIKE @JobSiteSequence" : "")
                    //+ (Employee > 0 ? " AND a.EMP_SEQ = @EmpSequence" : "")
                    + ") main"
                    //+ " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END";
                    + (string.IsNullOrEmpty(parent_id) ? " where PARENT_ID IS NULL" : " WHERE PARENT_ID = @parent_id");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeViewInfo info = new TreeViewInfo();
                        info.Id = Convert.IsDBNull(dr[i]["ID"]) ? "" : (dr[i]["ID"]).ToString();
                        info.Name = Convert.IsDBNull(dr[i]["NAME"]) ? "" : (dr[i]["NAME"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? "" : (dr[i]["PARENT_ID"]).ToString();
                        info.Descr = Convert.IsDBNull(dr[i]["DESCRIPTION"]) ? "" : (dr[i]["DESCRIPTION"]).ToString();
                        info.Children_count = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }
        public IList<TreeViewInfo> TreeRoyalItemRequest(string EmployeeType, string ReguestYear, string parent_id)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<TreeViewInfo> infoList = new List<TreeViewInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@parent_id", parent_id);
                dbManager.AddParameters(2, "@ReguestYear", ReguestYear);
                dbManager.AddParameters(3, "@EmpTypeSequence", EmployeeType);

                strSQL = "SELECT main.* FROM( "
                        + " SELECT CAST('0' AS CHAR) AS ID"
                             + ", CAST(CONCAT('รายชื่อผู้มีสิทธิ์ ฯ ปีงบประมาณ ', @ReguestYear) AS CHAR) AS NAME"
                             + ", NULL AS PARENT_ID"
                             + ",(select count(royal_item_seq) "
                                + " from hrm_ps_royal_item_requests"
                                + " where record_status not like @DeleteRecord"
                                + " and req_year = @ReguestYear"
                             + " ) AS CHILDREN_COUNT"
                             + ", '../Images/Buttons/listItemArrow.gif' AS IMAGE_URL"
                        + " UNION ALL "
                        + " SELECT CAST(CONCAT('0', rq.ROYAL_ITEM_SEQ) AS CHAR) AS ID"
                        + ", ri.ROYAL_ITEM_NAME AS NAME"
                        + ", CAST('0' AS CHAR) AS PARENT_ID"
                        + ", (select count(req_seq) from hrm_ps_royal_item_requests"
                                + " where royal_item_seq = rq.royal_item_seq"
                                + " and emp_seq not in (select emp_seq from hrm_ps_royal_item_histories"
                                    + " where year(received_date) + 543 = @ReguestYear )"
                                + ") AS CHILDREN_COUNT"
                        + ", '../Images/Buttons/listItemArrow.gif' AS IMAGE_URL"
                        + " FROM HRM_PS_ROYAL_ITEM_REQUESTS rq "
                        + " LEFT JOIN HRM_PS_ROYAL_ITEMS ri ON rq.ROYAL_ITEM_SEQ = ri.ROYAL_ITEM_SEQ"
                        + " WHERE rq.RECORD_STATUS not like @DeleteRecord"
                            + " AND rq.REQ_YEAR = @ReguestYear"
                            //+ " AND e.EMP_TYPE_SEQ = @EmpTypeSequence"
                        + " GROUP BY rq.ROYAL_ITEM_SEQ"
                    + " UNION ALL "
                        + "SELECT CAST(rq.REQ_SEQ AS CHAR) AS ID"
                        + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS NAME"
                        + ", CAST(CONCAT('0', rq.ROYAL_ITEM_SEQ) AS CHAR) AS PARENT_ID"
                        + ", 0 AS CHILDREN_COUNT"
                        + ", '../Images/Buttons/listItemArrow.gif' AS IMAGE_URL"
                    + " FROM HRM_PS_ROYAL_ITEM_REQUESTS rq "
                    + " LEFT JOIN HRM_PS_EMPLOYEES e ON rq.EMP_SEQ = e.EMP_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " WHERE rq.RECORD_STATUS not like @DeleteRecord"
                        + " AND rq.REQ_YEAR = @ReguestYear"
                        + " AND e.EMP_TYPE_SEQ = @EmpTypeSequence"
                        + " AND rq.EMP_SEQ NOT IN (select emp_seq from hrm_ps_royal_item_histories"
                                    + " where year(received_date) + 543 = @ReguestYear )"
                    + ") main"
                    //+ " WHERE CASE WHEN @parent_id = '' THEN PARENT_ID IS NULL ELSE PARENT_ID = @parent_id END";
                    + (string.IsNullOrEmpty(parent_id) ? " where PARENT_ID IS NULL" : " WHERE PARENT_ID = @parent_id");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        TreeViewInfo info = new TreeViewInfo();
                        info.Id = Convert.IsDBNull(dr[i]["ID"]) ? "" : (dr[i]["ID"]).ToString();
                        info.Name = Convert.IsDBNull(dr[i]["NAME"]) ? "" : (dr[i]["NAME"]).ToString();
                        info.Parent_id = Convert.IsDBNull(dr[i]["PARENT_ID"]) ? "" : (dr[i]["PARENT_ID"]).ToString();
                        //info.Descr = Convert.IsDBNull(dr[i]["DESCRIPTION"]) ? "" : (dr[i]["DESCRIPTION"]).ToString();
                        info.Children_count = Convert.ToInt32(dr[i]["CHILDREN_COUNT"]);
                        info.Image_url = Convert.IsDBNull(dr[i]["IMAGE_URL"]) ? null : (dr[i]["IMAGE_URL"]).ToString();

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            finally
            {
                dbManager.Dispose();
            }
            return infoList;
        }
    }
}
